--查看分区函数
select * from sys.partition_functions;
--查看分区方案
select * from sys.partition_schemes;

--根据临界值和分区函数名称查询某个时间的分区函数是否已存在
select prv.value 
from sys.partition_functions pf,sys.partition_range_values prv 
where pf.function_id = prv.function_id and pf.name='pro_csi_part_fun' 
  and prv.value = 5;
  
--查询某个分区值的分区编号
select $PARTITION.pro_csi_part_fun(5);

--根据分区编号查询表数据,check_scheme_id表示分区表的时间属性字段
select *
from cost.pro_cost_cal_serve_indirect 
where $PARTITION.pro_csi_part_fun(check_scheme_id)=6;

--查看文件组
select * from sys.filegroups;

-- 查看文件
select * from sysfiles;

-- 使用ALTER PARTITION SCHEME来指定下一个分区文件组
ALTER PARTITION SCHEME partition_scheme_namez  
NEXT USED [ filegroup_name ] 
-- partition_scheme_name 这个参数指定了需要修改的分区方案名
-- NEXT USED关键字把下一个文件组列入使用队列，让任何新的分区使用
alter partition scheme pro_csi_scheme next used [pcsi_6]


-- 修改分区函数
ALTER PARTITION FUNCTION partition_function_name()  
{   
    SPLIT RANGE ( boundary_value )  
  | MERGE RANGE ( boundary_value )  
} 

-- SPLIT RANGE通过定义一个新的边界值来创建一个新的分区。MERGE RANGE用于移除一个既有的分区
-- 拆分一个新分区
alter partition function pro_csi_part_fun() split range(6);


--删除分区文件
ALTER DATABASE [DemoOneFile] REMOVE FILE DemoOneFile02--删除文件
ALTER DATABASE [DemoOneFile] REMOVE FILEGROUP PRIMARY02--删除文件组


-- 创建分区对应普通表
create table cost.pcsi_invalid_6(
  id int identity(1,1) not null,
  cal_res_id int  null,
  check_scheme_id int null,
  organization_id int null,
  hospital_structure_id int null,
  organization_cal_method int null,
  serve_item_code varchar(20) null,
  cost_item_code varchar(20) null,
  number decimal(18,4) null,
  price decimal(18,4) null,
  indirect_cost_bk decimal(30,18) null,
  indirect_cost_gl decimal(30,18) null,
  indirect_cost_yf decimal(30,18) null,
  medical_cost_type int null,
  create_user_code varchar(20) null,
  create_time datetime null,
  update_user_code varchar(20) null,
  update_time datetime null,
  delete_flag tinyint null,
  calculate_method int null,
  medical_signage int default null
) on pcsi_6;

-- ALTER TABLE 表名 SWITCH PARTITION 源分区 TO 目标分区;
ALTER TABLE cost.pro_cost_cal_serve_indirect SWITCH PARTITION 6 TO cost.pcsi_temp_6;

-- 普通表切换到分区表(特别注意一定要增加约束)
ALTER TABLE cost.pcsi_temp_6 SWITCH TO cost.pro_cost_cal_serve_indirect PARTITION 6;

alter table cost.pcsi_temp_6 add constraint ck_check_scheme_id
check(check_scheme_id>5 and check_scheme_id<=6 and check_scheme_id is not null);

-- 分区索引重建
alter index all on cost.pro_cost_cal_serve_indirect rebuild partition = 74;


/************************************************************/
-- 1.创建分区函数
create partition function pro_csi_part_fun(int) as range left for values(1,2,3,4,5);


-- 创建分区函数
declare @num int = 1;
declare @part_id_str nvarchar(max) = '';
declare @part_fun_sql nvarchar(max) = 'create partition function pro_csi_part_fun(int) as range left for values(';
begin

	while @num <= 100
    begin
	   set @part_id_str = concat(@part_id_str, cast(@num as varchar(3)), ',');
       set @num = @num + 1;
    end;

	set @part_id_str = left(@part_id_str, len(@part_id_str) - 1);
	set @part_fun_sql = concat(@part_fun_sql, @part_id_str, ');');

	exec sp_executesql @part_fun_sql;
end;

-- 如果需要删除
-- drop partition function pro_csi_part_fun;
/************************************************************/


/************************************************************/
-- 2.创建文件组
-- ALTER DATABASE YourDatabaseName ADD FILEGROUP Sales_2020;
alter database cost_langfangdsyiyuan add filegroup pcsi_1;
alter database cost_langfangdsyiyuan add filegroup pcsi_2;
alter database cost_langfangdsyiyuan add filegroup pcsi_3;
alter database cost_langfangdsyiyuan add filegroup pcsi_4;
alter database cost_langfangdsyiyuan add filegroup pcsi_5;
alter database cost_langfangdsyiyuan add filegroup pcsi_6;

-- 创建文件组
declare @num int = 1;
declare @file_group_sql nvarchar(max) = '';
begin
	while @num <= 101
    begin
	   set @file_group_sql = concat('alter database ', DB_NAME(), concat(' add filegroup pcsi_', @num, ';'));
	   exec sp_executesql @file_group_sql;
       set @num = @num + 1;
    end;
end;
/************************************************************/



/************************************************************/
-- 3.创建文件
alter database perf1_189 add file(
   name=pro_cost_cal_serve_indirect_1_data, 
   filename='D:\software\sql_server_2014\MSSQL12.MSSQLSERVER\MSSQL\DATA\pcsi_1_data.ndf',
   size=10mb,
   maxsize=unlimited,
   filegrowth=10mb
) to filegroup pro_cost_cal_serve_indirect_1;

alter database cost_langfangdsyiyuan add file(name=pcsi_1_data, filename='D:\附加数据库1\pcsi_1_data.ndf',size=10mb,maxsize=unlimited,filegrowth=10mb) to filegroup pcsi_1;
alter database cost_langfangdsyiyuan add file(name=pcsi_2_data, filename='D:\附加数据库1\pcsi_2_data.ndf',size=10mb,maxsize=unlimited,filegrowth=10mb) to filegroup pcsi_2;
alter database cost_langfangdsyiyuan add file(name=pcsi_3_data, filename='D:\附加数据库1\pcsi_3_data.ndf',size=10mb,maxsize=unlimited,filegrowth=10mb) to filegroup pcsi_3;
alter database cost_langfangdsyiyuan add file(name=pcsi_4_data, filename='D:\附加数据库1\pcsi_4_data.ndf',size=10mb,maxsize=unlimited,filegrowth=10mb) to filegroup pcsi_4;
alter database cost_langfangdsyiyuan add file(name=pcsi_5_data, filename='D:\附加数据库1\pcsi_5_data.ndf',size=10mb,maxsize=unlimited,filegrowth=10mb) to filegroup pcsi_5;
alter database cost_langfangdsyiyuan add file(name=pcsi_6_data, filename='D:\附加数据库1\pcsi_6_data.ndf',size=10mb,maxsize=unlimited,filegrowth=10mb) to filegroup pcsi_6;


-- 创建文件
declare @num int = 1;
declare @file_name nvarchar(255) = '';
declare @file_path nvarchar(max) = '';
declare @file_sql nvarchar(max) = '';
declare @file_separator nvarchar(2) = '';

begin
    select top 1 @file_name = filename from sysfiles where groupid = 1;

	if charindex('\', @file_name) > 0
	   begin
	     set @file_path = substring(@file_name, 0, (len(@file_name) - charindex('\', reverse(@file_name)) + 1));
		 set @file_separator = '\';
	   end;

	if charindex('\', @file_name) = 0
	   begin
	     set @file_path = substring(@file_name, 0, (len(@file_name) - charindex('/', reverse(@file_name)) + 1));
		 set @file_separator = '/';
	   end

	while @num <= 101
    begin
	   set @file_sql = concat(concat('alter database ', DB_NAME(), concat(' add file(name=pcsi_', @num, '_data, filename=''')), concat(@file_path, concat(@file_separator,'pcsi_'), @num), concat('_data.ndf'',size=10mb,maxsize=unlimited,filegrowth=10mb) to filegroup pcsi_', @num, ';'));
	   exec sp_executesql @file_sql;
       set @num = @num + 1;
    end;
end;

/************************************************************/



/************************************************************/
--创建分区方案
create partition scheme pro_csi_scheme as partition pro_csi_part_fun to(pcsi_1,pcsi_2,pcsi_3,pcsi_4,pcsi_5,pcsi_6);

-- 创建分区方案
declare @num int = 1;
declare @scheme_id_str nvarchar(max) = '';
declare @scheme_sql nvarchar(max) = 'create partition scheme pro_csi_scheme as partition pro_csi_part_fun to(';
begin

	while @num <= 101
    begin
	   set @scheme_id_str = concat(concat(@scheme_id_str, 'pcsi_'), cast(@num as varchar(3)), ',');
       set @num = @num + 1;
    end;

	set @scheme_id_str = left(@scheme_id_str, len(@scheme_id_str) - 1);
	set @scheme_sql = concat(@scheme_sql, @scheme_id_str, ');');

	exec sp_executesql @scheme_sql;
end;

/************************************************************/



/************************************************************/
-- 创建分区表
create table cost.pro_cost_cal_serve_indirect_new(
  id int identity(1,1) not null,
  cal_res_id int  null,
  check_scheme_id int null,
  organization_id int null,
  hospital_structure_id int null,
  organization_cal_method int null,
  serve_item_code varchar(20) null,
  cost_item_code varchar(20) null,
  number decimal(18,4) null,
  price decimal(18,4) null,
  indirect_cost_bk decimal(30,18) null,
  indirect_cost_gl decimal(30,18) null,
  indirect_cost_yf decimal(30,18) null,
  medical_cost_type int null,
  create_user_code varchar(20) null,
  create_time datetime null,
  update_user_code varchar(20) null,
  update_time datetime null,
  delete_flag tinyint null,
  calculate_method int null,
  medical_signage int default null
) on pro_csi_scheme(check_scheme_id);

create nonclustered index idx_pro_cost_cal_serve_indirect
on cost.pro_cost_cal_serve_indirect_new(id);

create nonclustered index idx_non_clu_pro_cost_cal_serve_indirect
on cost.pro_cost_cal_serve_indirect_new(check_scheme_id, organization_id);

create nonclustered index idx_non_clu_pro_cost_cal_serve_indirect2
on cost.pro_cost_cal_serve_indirect_new(cal_res_id);

EXEC sp_addextendedproperty
'MS_Description', N'项目成本核算主表id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'cal_res_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'核算方案主表id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'check_scheme_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'科室id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'organization_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'院区id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'hospital_structure_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'核算方法',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'organization_cal_method'
GO

EXEC sp_addextendedproperty
'MS_Description', N'服务项目编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'serve_item_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'成本项目编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'cost_item_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'数量',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'number'
GO

EXEC sp_addextendedproperty
'MS_Description', N'单价',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'price'
GO

EXEC sp_addextendedproperty
'MS_Description', N'本科-间接',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'indirect_cost_bk'
GO

EXEC sp_addextendedproperty
'MS_Description', N'管理分摊',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'indirect_cost_gl'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医辅分摊',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'indirect_cost_yf'
GO

EXEC sp_addextendedproperty
'MS_Description', N'成本维度',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'medical_cost_type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'create_user_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'create_time'
GO

EXEC sp_addextendedproperty
'MS_Description', N'修改人',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'update_user_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'修改时间',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'update_time'
GO

EXEC sp_addextendedproperty
'MS_Description', N'删除标记:0.存在,1.已删除',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'delete_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'计算方法:1303.当量法,1801.时间驱动作业成本法',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'calculate_method'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医护标识:4301.医生项目,4302.护理项目',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'medical_signage'
GO

EXEC sp_addextendedproperty
'MS_Description', N'项目间接成本值表',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new'
GO

-- 将原始表的数据导入新的分区表中
SET IDENTITY_INSERT cost.pro_cost_cal_serve_indirect_new ON;

insert into cost.pro_cost_cal_serve_indirect_new(
       id, cal_res_id, check_scheme_id, organization_id,
       hospital_structure_id, organization_cal_method, serve_item_code, cost_item_code,
	   number, price, indirect_cost_bk, indirect_cost_gl,
	   indirect_cost_yf, medical_cost_type, create_time, update_time,
	   delete_flag, calculate_method, medical_signage
       )
select id, cal_res_id, check_scheme_id, organization_id,
       hospital_structure_id, organization_cal_method, serve_item_code, cost_item_code,
	   number, price, indirect_cost_bk, indirect_cost_gl,
	   indirect_cost_yf, medical_cost_type, create_time, update_time,
	   delete_flag, calculate_method, medical_signage
from cost.pro_cost_cal_serve_indirect;

SET IDENTITY_INSERT cost.pro_cost_cal_serve_indirect_new OFF;

-- 验证数据是否成功导入到新的分区表中（执行一些查询）
-- select count(1) from cost.pro_cost_cal_serve_indirect_new;

-- 删除原始表并将新的分区表重命名为原始表的名称
drop table cost.pro_cost_cal_serve_indirect;
exec sp_rename 'cost.pro_cost_cal_serve_indirect_new', 'pro_cost_cal_serve_indirect';

/************************************************************/


-- 查看表约束
SELECT   
    t.name AS TableName,  
	c.*
    --c.name AS ConstraintName,  
    --c.type_desc AS ConstraintType,  
    --c.is_disabled AS IsDisabled,  
    --c.is_not_trusted AS IsNotTrusted,  
    --c.definition AS Definition  
FROM   
    sys.tables t  
INNER JOIN   
    sys.columns c ON t.object_id = c.object_id  
INNER JOIN   
    sys.sysconstraints sc ON t.object_id = sc.id AND c.column_id = sc.colid  
WHERE 1=1  
    -- and t.name = 'YourTableName' -- 替换为要查看的分区表名称 
	and t.name = 'pcsi_temp_6' -- 替换为要查看的分区表名称 

	--  pro_cost_cal_serve_indirect
	-- pcsi_temp_6
	
--查看分区范围值
select partition_function = f.name
         ,t.partition
         ,t.minval
         ,value = case when f.boundary_value_on_right=1 then '<= val <' else '< val <=' end
         ,t.maxval
     from (
           select h.function_id
                 ,partition = h.boundary_id
                 ,minval    = l.value
                 ,maxval    = h.value
             from sys.partition_range_values h
                     left join sys.partition_range_values l
               on h.function_id = l.function_id and h.boundary_id = l.boundary_id + 1

           union all

           select function_id
                 ,partition = max(boundary_id) + 1
                 ,minval    = max(value)
                 ,maxval    = null
             from sys.partition_range_values
            group by function_id
          ) t
              inner join sys.partition_functions f
         on t.function_id = f.function_id
      where f.function_id = 65542
         or 65542 is null
      order by 1, 2
	  
-- 查询列出了数据库中所有分区表及其相应的文件组
SELECT   
    t.name AS TableName,  
    p.partition_number AS PartitionNumber,  
    fg.name AS FileGroupName  
FROM   
    sys.tables t  
JOIN   
    sys.partitions p ON t.object_id = p.object_id  
JOIN   
    sys.allocation_units a ON p.partition_id = a.container_id  
JOIN   
    sys.filegroups fg ON a.data_space_id = fg.data_space_id  
WHERE   
    t.type_desc = 'USER_TABLE' -- 仅查看用户表  
    AND p.index_id IN (0,1) -- 仅查看聚集索引和非聚集索引    
ORDER BY   
    t.name, p.partition_number;
	  
	  



------------------------------------ 分区示例开始---------------------------------------------
-- 1.项目间接成本值表cost.pro_cost_cal_serve_indirect改为分区表
-- 创建分区函数
declare @num int = 1;
declare @part_id_str nvarchar(max) = '';
declare @part_fun_sql nvarchar(max) = 'create partition function pro_csi_part_fun(int) as range left for values(';
begin

	while @num <= 100
    begin
	   set @part_id_str = concat(@part_id_str, cast(@num as varchar(3)), ',');
       set @num = @num + 1;
    end;

	set @part_id_str = left(@part_id_str, len(@part_id_str) - 1);
	set @part_fun_sql = concat(@part_fun_sql, @part_id_str, ');');

	exec sp_executesql @part_fun_sql;
end;

-- 创建文件组
declare @num int = 1;
declare @file_group_sql nvarchar(max) = '';
begin
	while @num <= 101
    begin
	   set @file_group_sql = concat('alter database ', DB_NAME(), concat(' add filegroup pcsi_', @num, ';'));
	   exec sp_executesql @file_group_sql;
       set @num = @num + 1;
    end;
end;

-- 创建文件
declare @num int = 1;
declare @file_name nvarchar(255) = '';
declare @file_path nvarchar(max) = '';
declare @file_sql nvarchar(max) = '';
declare @file_separator nvarchar(2) = '';

begin
    select top 1 @file_name = filename from sysfiles where groupid = 1;

	if charindex('\', @file_name) > 0
	   begin
	     set @file_path = substring(@file_name, 0, (len(@file_name) - charindex('\', reverse(@file_name)) + 1));
		 set @file_separator = '\';
	   end;

	if charindex('\', @file_name) = 0
	   begin
	     set @file_path = substring(@file_name, 0, (len(@file_name) - charindex('/', reverse(@file_name)) + 1));
		 set @file_separator = '/';
	   end

	while @num <= 101
    begin
	   set @file_sql = concat(concat('alter database ', DB_NAME(), concat(concat(' add file(name=', DB_NAME(), '_pcsi_'), @num, '_data, filename=''')), concat(@file_path, concat(@file_separator, DB_NAME(), '_pcsi_'), @num), concat('_data.ndf'',size=1mb,maxsize=unlimited,filegrowth=20mb) to filegroup pcsi_', @num, ';'));
	   exec sp_executesql @file_sql;
       set @num = @num + 1;
    end;
end;

-- 创建分区方案
declare @num int = 1;
declare @scheme_id_str nvarchar(max) = '';
declare @scheme_sql nvarchar(max) = 'create partition scheme pro_csi_scheme as partition pro_csi_part_fun to(';
begin

	while @num <= 101
    begin
	   set @scheme_id_str = concat(concat(@scheme_id_str, 'pcsi_'), cast(@num as varchar(3)), ',');
       set @num = @num + 1;
    end;

	set @scheme_id_str = left(@scheme_id_str, len(@scheme_id_str) - 1);
	set @scheme_sql = concat(@scheme_sql, @scheme_id_str, ');');

	exec sp_executesql @scheme_sql;
end;

-- 创建分区表
create table cost.pro_cost_cal_serve_indirect_new(
  id int identity(1,1) not null,
  cal_res_id int  null,
  check_scheme_id int null,
  organization_id int null,
  hospital_structure_id int null,
  organization_cal_method int null,
  serve_item_code varchar(50) null,
  cost_item_code varchar(20) null,
  number decimal(18,4) null,
  price decimal(18,4) null,
  indirect_cost_bk decimal(30,18) null,
  indirect_cost_gl decimal(30,18) null,
  indirect_cost_yf decimal(30,18) null,
  medical_cost_type int null,
  create_user_code varchar(20) null,
  create_time datetime null,
  update_user_code varchar(20) null,
  update_time datetime null,
  delete_flag tinyint null,
  calculate_method int null,
  medical_signage int default null
) on pro_csi_scheme(check_scheme_id);

create nonclustered index idx_pro_cost_cal_serve_indirect
on cost.pro_cost_cal_serve_indirect_new(id);

create nonclustered index idx_non_clu_pro_cost_cal_serve_indirect
on cost.pro_cost_cal_serve_indirect_new(check_scheme_id, organization_id);

create nonclustered index idx_non_clu_pro_cost_cal_serve_indirect2
on cost.pro_cost_cal_serve_indirect_new(cal_res_id);

EXEC sp_addextendedproperty
'MS_Description', N'项目成本核算主表id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'cal_res_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'核算方案主表id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'check_scheme_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'科室id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'organization_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'院区id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'hospital_structure_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'核算方法',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'organization_cal_method'
GO

EXEC sp_addextendedproperty
'MS_Description', N'服务项目编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'serve_item_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'成本项目编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'cost_item_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'数量',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'number'
GO

EXEC sp_addextendedproperty
'MS_Description', N'单价',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'price'
GO

EXEC sp_addextendedproperty
'MS_Description', N'本科-间接',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'indirect_cost_bk'
GO

EXEC sp_addextendedproperty
'MS_Description', N'管理分摊',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'indirect_cost_gl'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医辅分摊',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'indirect_cost_yf'
GO

EXEC sp_addextendedproperty
'MS_Description', N'成本维度',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'medical_cost_type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'create_user_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'create_time'
GO

EXEC sp_addextendedproperty
'MS_Description', N'修改人',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'update_user_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'修改时间',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'update_time'
GO

EXEC sp_addextendedproperty
'MS_Description', N'删除标记:0.存在,1.已删除',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'delete_flag'
GO

EXEC sp_addextendedproperty
'MS_Description', N'计算方法:1303.当量法,1801.时间驱动作业成本法',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'calculate_method'
GO

EXEC sp_addextendedproperty
'MS_Description', N'医护标识:4301.医生项目,4302.护理项目',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'medical_signage'
GO

EXEC sp_addextendedproperty
'MS_Description', N'项目间接成本值表',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new'
GO

-- 将原始表的数据导入新的分区表中
SET IDENTITY_INSERT cost.pro_cost_cal_serve_indirect_new ON;

insert into cost.pro_cost_cal_serve_indirect_new(
       id, cal_res_id, check_scheme_id, organization_id,
       hospital_structure_id, organization_cal_method, serve_item_code, cost_item_code,
	   number, price, indirect_cost_bk, indirect_cost_gl,
	   indirect_cost_yf, medical_cost_type, create_time, update_time,
	   delete_flag, calculate_method, medical_signage
       )
select id, cal_res_id, check_scheme_id, organization_id,
       hospital_structure_id, organization_cal_method, serve_item_code, cost_item_code,
	   number, price, indirect_cost_bk, indirect_cost_gl,
	   indirect_cost_yf, medical_cost_type, create_time, update_time,
	   delete_flag, calculate_method, medical_signage
from cost.pro_cost_cal_serve_indirect;

SET IDENTITY_INSERT cost.pro_cost_cal_serve_indirect_new OFF;

-- 验证数据是否成功导入到新的分区表中（执行一些查询）
-- select count(1) from cost.pro_cost_cal_serve_indirect_new;

-- 删除原始表并将新的分区表重命名为原始表的名称
drop table cost.pro_cost_cal_serve_indirect;
exec sp_rename 'cost.pro_cost_cal_serve_indirect_new', 'pro_cost_cal_serve_indirect';

------------------------------------ 分区示例结束---------------------------------------------